package com.neu.shop.controller.front;

import java.text.MessageFormat;

public class Ttt {
    public static String getSql(String unit, String table, String alias) {
        String tpl = "select so.id,so.code, sum(ifnull({2}.num,0)) from sys_organ so LEFT JOIN (select {0},count(1) as num from {1} group by {0}) {2} on {2}.{0} in (select id from sys_organ l where l.id_path REGEXP so.id ) where so.type = 1 and exists ( select 1 from sys_organ o where so.parent_id = o.id and o.type = 0 ) group by so.id, so. code";
        return MessageFormat.format(tpl, unit, table, alias);
    }
    public static void main(String[] args) {
        System.out.println("//巡检");
        System.out.println(getSql("subordinate_unit", "check_maintaskpaper", "ck"));
        System.out.println("//需求单");
        System.out.println(getSql("presenter_unit_id", "demand_apply_info", "dai"));
        System.out.println("//作业计划单");
        System.out.println(getSql("organize_no", "work_plan_info", "wpi"));
        System.out.println("//等级保护");
        System.out.println(getSql("apply_baid", "grading_info", "gi"));
        System.out.println("//机房进出");
        System.out.println(getSql("application_unit_id", "machine_room_info", "mri"));
        System.out.println("//事件");
        System.out.println(getSql("belongs_org_id", "sp_event_info", "sei"));
        System.out.println("//请求");
        System.out.println(getSql("apply_org_id", "request_info", "ri"));
        System.out.println("//变更");
        System.out.println(getSql("change_supply_person_unit_id", "change_supply_info", "csi"));
        System.out.println("//交接班");
        System.out.println(getSql("affiliation_unit_id", "itsm_handover_manage", "ihm"));
        System.out.println("//任务");
        System.out.println(getSql("apply_organize_no", "assignment_info", "ai"));
        System.out.println("//知识");
        System.out.println(getSql("application_unit_id", "zsgl_knowledge", "zk"));
        System.out.println("//投运");
        System.out.println(getSql("proposer_unit_id", "commission_apply", "ca"));
        System.out.println("//移交");
        System.out.println(getSql("proposer_unit_id", "handover_apply", "ha"));
        System.out.println("//缺陷");
        System.out.println(getSql("peosose_person_unit_id", "defect_reg", "dr"));
        System.out.println("//发布");
        System.out.println(getSql("organise_id", "release_info", "rsi"));
        System.out.println("//退运");
        System.out.println(getSql("proposer_org_id", "exporter", "ex"));
    }
}
